package nccloud.web.hrpbm.sjxm.sjxmh.action;
import nc.itf.org.IOrgResourceCodeConst;
import nccloud.framework.web.processor.refgrid.RefQueryInfo;
import nccloud.framework.web.ui.meta.RefMeta;
import nccloud.web.refer.DefaultGridRefAction;

import java.time.LocalDate;
import java.util.Calendar;
/**
 * 
 * 档案特性生成默认参照
 * 
 * @author zhaoxwf
 * 
 */
public class SjxmhGridRefAction extends DefaultGridRefAction {
	
	public SjxmhGridRefAction() {
		super();
		setResourceCode(IOrgResourceCodeConst.ORG);
	}

	@Override
	public RefMeta getRefMeta(RefQueryInfo refQueryInfo) {
		RefMeta refMeta = new RefMeta();		
		setResourceCode(IOrgResourceCodeConst.ORG);
		refMeta.setCodeField("hopbillcode");
		refMeta.setNameField("name");
		refMeta.setExtraFields(new String[] {"applydept","price","money","spec","nnum"});
		refMeta.setPkField("pk_sjxm");
		refMeta.setTableName("hrpbm_sjxmh");
		
		refMeta.setMutilLangNameRef(true);
		return refMeta;
	}


	@Override
	public String getExtraSql(RefQueryInfo refQueryInfo, RefMeta refMeta) {
		StringBuffer sql = new StringBuffer();
		String officedept = refQueryInfo.getQueryCondition().get("officedept");// 表头 主管科室
		String pk_org = refQueryInfo.getQueryCondition().get("pk_org");//
		String applyyear = refQueryInfo.getQueryCondition().get("applyyear");//
		if(officedept == null || "".equals(officedept) || pk_org == null || "".equals(pk_org) ) {
			return sql.toString();
		}
		// 终止采购的项目不允许在进行申请  四级采购项目状态---> 终止采购 : 4
		sql.append("  and approvestatus  = 1 ");
		sql.append(" and def1 in ( "); // def1
		// 获取获取四级采购项目中对一个财务项目
		String buildsql = this.buildBaseDataContrastTables("CG", "b26fa3cb-4087-4027-a3b6-c83ab2a086a9", pk_org, officedept);
		sql.append(buildsql);
		sql.append(" ) ");
		// 一个采购项目只允许申请一次
		sql.append(this.buildQueryOneSQL(applyyear));
		return sql.toString();
	}

	/**
	 * 一个采购项目只允许申请一次
	 * @return
	 */
	protected String buildQueryOneSQL(String year) {
		StringBuilder sql = new StringBuilder();
		sql.append(" and pk_sjxm in (   " );
		sql.append("  SELECT  " );
		sql.append("    h.pk_sjxm  " );
		sql.append("  FROM  " );
		sql.append("    hrpbm_sjxmh h  " );
		sql.append("  left JOIN hrpbm_sjxmb b on h.pk_sjxm = b.pk_sjxm  " );
		sql.append("  where 1 = 1  and b.bdef1 <> 'Y' and b.ysyear = '"+year+"'  " );
		sql.append(" )");
		return sql.toString();
	}

	/**
	 * 获取部门对照关系sql
	 * @param exsystemcode
	 * @param bdclass
	 * @param pk_org
	 * @param pk_dept
	 * @return
	 */
	protected String buildBaseDataContrastTables(String exsystemcode, String bdclass,String pk_org, String pk_dept) {
		String sql = " select  " +
				"    dzb.exsysval sfcode " +
				" from xx_exsystem xe  " +
				" left join xx_bdcontra dzh on xe.pk_exsystem = dzh.exsystem " +
				" left join xx_bdcontra_b dzb on dzh.pk_contra = dzb.pk_contra " +
				" left join org_dept dept on dzb.bdcode = dept.code " +
				" where 1 = 1 and xe.dr =  0 " +
				"    and dzh.dr = 0 and dzb.dr = 0  " +
				"    and  xe.exsystemcode = '" + exsystemcode + "'  " +
				"    and dzh.bdclass = '" + bdclass + "' " +
				"    and dept.pk_dept  = '" + pk_dept + "' " +
				"    and dzh.pk_org = '" + pk_org + "' ";
		return sql;
	}

}
